from django.http import JsonResponse
from .models import FuturesHistory
from django.db import connection
import logging

# 获取自定义日志器（与 settings.py 中的 'app' 对应）
logger = logging.getLogger('app')
def futures_history_chart_data(request, ak_code, period):
    """
    获取期货历史数据用于图表展示
    :param request: HTTP请求
    :param ak_code: 期货代码
    :param period: 时间周期 (daily, weekly, monthly)
    :return: JSON格式的图表数据
    """
    try:
        # 获取查询参数
        limit = request.GET.get('limit', None)
        limit = int(limit)
        # 根据周期和限制获取数据
        if period == 'daily':
            # 日线数据，获取最近limit天的数据
            histories = FuturesHistory.objects.filter(
                ak_code=ak_code
            ).order_by('-trans_date')[:limit]
            # 重新按日期升序排列
            data_points = list(reversed(list(histories)))

        elif period == 'weekly':
            # 周线数据，获取最近limit周的数据
            sampled_data = get_sampled_data(ak_code, sample_rate=5, limit=limit)

            # 构造数据点列表
            data_points = []
            for item in sampled_data:
                # 创建一个类似FuturesHistory对象的结构
                class DataPoint:
                    def __init__(self, current, trans_date):
                        self.current = current
                        self.trans_date = trans_date

                data_point = DataPoint(item['current'], item['trans_date'])
                data_points.append(data_point)

            # 按日期升序排列
            data_points.sort(key=lambda x: x.trans_date)

        elif period == 'monthly':
            # 月线数据，获取最近limit个月的数据
            # sample_rate=20意味着每20条记录取1条，limit=60意味着最多取60条记录
            sampled_data = get_sampled_data(ak_code, sample_rate=21, limit=limit)

            # 构造数据点列表
            data_points = []
            for item in sampled_data:
                # 创建一个类似FuturesHistory对象的结构
                class DataPoint:
                    def __init__(self, current, trans_date):
                        self.current = current
                        self.trans_date = trans_date

                data_point = DataPoint(item['current'], item['trans_date'])
                data_points.append(data_point)

            # 按日期升序排列
            data_points.sort(key=lambda x: x.trans_date)

        else:
            # 默认使用日线数据
            histories = FuturesHistory.objects.filter(
                ak_code=ak_code
            ).order_by('-trans_date')[:limit]
            data_points = list(reversed(list(histories)))

        if not data_points:
            return JsonResponse({'error': '未找到相关数据'}, status=404)

        # 构造返回数据
        labels = []
        data = []

        for history in data_points:
            # 格式化日期显示
            date_str = str(history.trans_date)
            if len(date_str) == 8:  # YYYYMMDD
                formatted_date = f"{date_str[:4]}-{date_str[4:6]}-{date_str[6:]}"
            else:
                formatted_date = date_str

            labels.append(formatted_date)
            # 确保current字段不为空
            if history.current is not None:
                data.append(float(history.current))
            else:
                data.append(0.0)

        return JsonResponse({
            'labels': labels,
            'data': data
        })
    except Exception as e:
        return JsonResponse({'error': str(e)}, status=500)


def get_sampled_data(ak_code, sample_rate=5, limit=60):
    with connection.cursor() as cursor:
        sql = """
                    SELECT *
                    FROM (
                        SELECT 
                            f.*,
                            @row_num := @row_num + 1 AS rn
                        FROM futures_commodity_test f, 
                             (SELECT @row_num := 0) AS r
                        WHERE f.ak_code = %s
                        ORDER BY f.trans_date DESC
                    ) t
                    WHERE t.rn %% %s = 1
                    LIMIT %s
                """
        logger.info(sql % (ak_code, sample_rate, limit))

        cursor.execute(sql, [ak_code, sample_rate, limit])

        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]
# Create your views here.
